package com.appspot.aliga.service;

import java.text.SimpleDateFormat;
import java.util.Locale;

import com.appspot.aliga.Gameday;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class ALigaDBAdapter {

	public static final SimpleDateFormat dateFormat = new SimpleDateFormat(
			"yyyy-MM-dd HH:mm:ss.SSS", Locale.GERMAN);

	public static final String KEY_GAMEDAY_ID = "_id";
	public static final String KEY_GAMEDAY_LEAGUEID = "leagueID";
	public static final String KEY_GAMEDAY_DAY = "day";
	public static final String KEY_GAMEDAY_ISCOMPLETE = "isComplete";
	public static final String KEY_GAMEDAY_FROMDATE = "fromDate";
	public static final String KEY_GAMEDAY_TODATE = "toDate";

	public static final String KEY_MATCH_TEAM1NAME = "team1Name";
	public static final String KEY_MATCH_TEAM2NAME = "team2Name";
	public static final String KEY_MATCH_TEAM1RESULT = "team1Result";
	public static final String KEY_MATCH_TEAM2RESULT = "team2result";
	public static final String KEY_MATCH_MATCHDATE = "matchDate";
	public static final String KEY_MATCH_LEAGUEID = "leagueID";
	public static final String KEY_MATCH_GAMEDAY = "gameday";

	private static final String TAG = "ALigaDbAdapter";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	/**
	 * Database creation sql statement
	 */
	private static final String CREATE_GAMDAY_TABLE = "create table gamedays (_id integer primary key autoincrement, "
			+ "leagueID integer not null, day integer not null, isComplete integer not null, fromDate text, toDate text);";
	private static final String CREATE_MATCH_TABLE = "create table matches (_id integer primary key autoincrement, "
			+ "team1Name text not null, team2Name text not null, team1Result integer not null, team2result integer not null, matchDate text, leagueID integer not null, gameday integer not null);";

	private static final String DATABASE_NAME = "data";
	private static final String DATABASE_TABLE_GAMEDAYS = "gamedays";
	private static final String DATABASE_TABLE_MATCHES = "matches";
	private static final int DATABASE_VERSION = 2;

	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_GAMDAY_TABLE);
			db.execSQL(CREATE_MATCH_TABLE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS gamedays");
			db.execSQL("DROP TABLE IF EXISTS matches");
			onCreate(db);
		}
	}

	/**
	 * Constructor - takes the context to allow the database to be
	 * opened/created
	 * 
	 * @param ctx
	 *            the Context within which to work
	 */
	public ALigaDBAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	/**
	 * Open the notes database. If it cannot be opened, try to create a new
	 * instance of the database. If it cannot be created, throw an exception to
	 * signal the failure
	 * 
	 * @return this (self reference, allowing this to be chained in an
	 *         initialization call)
	 * @throws SQLException
	 *             if the database could be neither opened or created
	 */
	public ALigaDBAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public long saveGameday(Gameday gameday) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_GAMEDAY_DAY, gameday.getDay());
		initialValues.put(KEY_GAMEDAY_LEAGUEID, gameday.getLeagueID());
		initialValues.put(KEY_GAMEDAY_ISCOMPLETE, gameday.isComplete());
		initialValues.put(KEY_GAMEDAY_FROMDATE, dateFormat.format(gameday
				.getFromDate()));
		initialValues.put(KEY_GAMEDAY_TODATE, dateFormat.format(gameday
				.getToDate()));

		if (gameday.getId() == null) {
			return mDb.insert(DATABASE_TABLE_GAMEDAYS, null, initialValues);
		}

		else {
			return mDb.update(DATABASE_TABLE_GAMEDAYS, initialValues,
					KEY_GAMEDAY_ID + "=" + gameday.getId(), null);
		}

	}

	public boolean deleteGameday(Gameday gameday) {

		return mDb.delete(DATABASE_TABLE_GAMEDAYS, KEY_GAMEDAY_ID + "="
				+ gameday.getId(), null) > 0;
	}

	public Cursor fetchAllGamedays() {

		return mDb.query(DATABASE_TABLE_GAMEDAYS, new String[] {
				KEY_GAMEDAY_ID, KEY_GAMEDAY_DAY, KEY_GAMEDAY_LEAGUEID,
				KEY_GAMEDAY_ISCOMPLETE, KEY_GAMEDAY_FROMDATE,
				KEY_GAMEDAY_TODATE }, null, null, null, null, null);
	}

	public Cursor fetchGameday(long id) throws SQLException {

		Cursor mCursor =

		mDb.query(true, DATABASE_TABLE_GAMEDAYS, new String[] { KEY_GAMEDAY_ID,
				KEY_GAMEDAY_DAY, KEY_GAMEDAY_LEAGUEID, KEY_GAMEDAY_ISCOMPLETE,
				KEY_GAMEDAY_FROMDATE, KEY_GAMEDAY_TODATE }, KEY_GAMEDAY_ID
				+ "=" + id, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;

	}
}
